import pymysql


#
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     passwd='123456',
#     db='test02',
#     port=3306,
#     charset='utf8'
# )
#
# curor = db.cursor()
# sql = 'select * from dept where teptno = %s' % '2'
#
# curor.execute(sql)
# data = curor.fetchall()
# # print(data)
#
# for i in data:
#     print(i)
#
# curor.close()
# db.close()

class Dbutils:
    def __init__(self, host, user, passwd, db, port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.port = port
        self.charset = charset

    def update(self, sql_update, data_update=None):
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            passwd=self.passwd,
            db=self.db,
            port=self.port,
            charset=self.charset,
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql_update, data_update)
                db.commit()
                print('数据库操作成功！')
        except Exception as e:
            print('数据库操作错误：', e)

        finally:
            db.close()

    def select(self, sql_select, data_select=None):
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            passwd=self.passwd,
            db=self.db,
            port=self.port,
            charset=self.charset,
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql_select, data_select)
                data_all = cursor.fetchall()
                print(data_all)
                print('数据库查询成功!')
        except Exception as e:
            print('数据库查询失败：', e)
        finally:
            db.close()


if __name__ == '__main__':
    localdb = Dbutils('localhost', 'root', '123456', 'test02')

    # 测试更新操作
    sql_update = 'create table if not exists person(username varchar(20),age int,sex char(4),high double(3, 2) )'
    localdb.update(sql_update)

    # 编程实现：循环输入10个人的信息（姓名，年龄，性别，身高），并存储到数据库中
    i = 0
    data_update = []
    while i < 10:
        a = input('请输入姓名：')
        b = int(input('请输入年龄：'))
        c = input('请输入性别：')
        d = float(input('请输入身高：'))
        i+=1
        data_update.append((a, b, c, d))
    sql_update = f'insert into person(username,age,sex,high) values (%s,%s,%s,%s)'
    for i in data_update:
        localdb.update(sql_update, i)